<?php
/**
* 数据库操作类，使用pdo
*/
defined('WALL_CODE') or exit('No permission resources.'); 
class Sql{

	public $_link = NULL;//数据库链接
	public $_ps = NULL;//PDO statement 对象
	public $_param = NULL;//构造器
	
	private $_table = NULL;//数据表名

	private $_build;//存储用于构造sql的参数
	private $_queryType;//执行的操作类型
	private $_sql;//执行的sql语句
	
	public function __construct($tableName){
		//初始化build成员变量，防止出现undefined index的notice
		$this->_build = array('fields'=>'','alias'=>'','join'=>'','where'=>'','group'=>'','order'=>'','limit'=>'');
		$this->_table = $tableName;
		$this->connect();
	}
	//数据库连接操作
	protected function connect(){
		$config = $this->config();
		$config['dbName'] = $config['dbPrefix'].'_'.$config['dbName'];
		$this->_link = $this->conn_db($config['dbHost'],$config['dbUser'],$config['dbPasswd'],$config['dbName']);
	}
	//数据库链接函数使用pdo进行连接
	protected function conn_db($dbHost,$dbUser,$dbPasswd,$dbName){
		$dbh = "mysql:host=$dbHost;dbname=$dbName";
		try {
			$pdo = new PDO($dbh,$dbUser,$dbPasswd);
		} catch (PDOException  $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
		}
		return $pdo;
	}

	/**
	 * 执行初始化
	 * @param $sql
	 * @param string $params
	 */
	private function _init($sql ,$params=""){
		if(!empty($this->_link)) $this->connect();
		try {
			$this->_ps = $this->_link->prepare($sql);
			$this->bindmore($params);
			foreach ($this->_param as $key => $value) {
				switch (true) {
					case is_int($value):
						$type = PDO::PARAM_INT;
						break;
					case is_bool($value):
						$type = PDO::PARAM_BOOL;
						break;
					case is_null($value):
						$type = PDO::PARAM_NULL;
						break;
					default:
						$type = PDO::PARAM_STR;
						break;
				}
				$this->_ps->bindParam($key ,$this->_param[$key] , $type);
			}
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
		}

	}
	/**
	 * 绑定数据
	 * @param unknown $para
	 * @param unknown $value
	 */
	public function bind($para ,&$value){
		$this->_param[':'.$para] = $value;
	}
	public function bindmore($parray){
		if(empty($this->_param) && is_array($parray)){
			foreach ($parray as $key => $value) {
				$this->bind($key ,$parray[$key]);
			}
		}

	}
	//执行数据库sql查询
	//默认返回索引为结果集列名的数组
	public function query($sql ,$params = NULL ,$model = PDO::FETCH_ASSOC){
		$data = array();
		$sql = trim($sql);
		$res = $this->_link->query($sql, $model);
		$data = $res->fetchAll();
		return $data;
	}
	//数据更新操作
	public function update($data ,$where){
		$set = '';
		$w = '';
		$sql = "UPDATE `".$this->_table."` SET";
		foreach ($data as $key => $value) {
			$set.="`$key`=:$key,";
			//$col.="`$key`,";
			//$val.="`$key`,";
		}
		$set = rtrim($set, ",");
		//$val = rtrim($val, ",");
		$sql.=$set.' WHERE ';
		foreach ($where as $key => $value) {
			$w.=" and `$key`=:$key";
			$data[$key] = $value;
		}
		$w = ltrim($w, " and ");
		$sql.=$w.'';
		try {
			$this->_init($sql ,$data);
			$this->_ps->execute();
//			echo $sql;
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
		}
	}
	/**
	 * 数据库写入操作
	 * @param string $table
	 * @param array $data
	 */
	public function insert($data){
		$val = '';
		$col = '';
		$sql = "INSERT INTO `".$this->_table."`(";
		foreach ($data as $key => $value) {
			$col.="`$key`,";
			$val.=":$key,";
		}
		$col=rtrim($col,",");
		$val=rtrim($val,",");
		$sql.=$col.") VALUES (".$val.")";
		try {
			//echo "$sql";
			$this->_init($sql ,$data);
			//$this->_ps;
			$this->_ps->execute();
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
		}
	}
	//数据库查询操作
	public function select(){
		$this->_queryType = 's';
		$this->generateSql();
		return $this->query($this->_sql);
	}
	//数据库删除操作
	public function delete($where=''){
		$sql = "DELETE FROM ".$this->_table;
		$where = $where == '' ? '' : ' WHERE '.$where;
		$sql .= $where;
		try {
			return $this->query($sql);
		} catch (PDOException $e) {
			print "Error!: " . $e->getMessage() . "<br/>";
			die();
		}
	}
	/**
	 * 设置数需要查询的表的名称
	 * @param string $tablename
	 */
	public function set_table_name($tablename) {
		$this->_table = $tablename;
	}
	//简化select
	public function selectall(){
		$this->_link->query("SELECT * FROM $this->_table");
	}
	//载入配置参数
	protected function config(){
		return Dispatcher::$_config['db'];
	}

	/**
	 * 扩展链式操作
	 */
	public function fields($str = '*') {
		$this->_build['fields'] = $str;
		return $this;
	}

	public function where($str) {
		$this->_build['where'] = ' WHERE '.$str;
		return $this;
	}

	public function alias($str) {
		if ($str) {
			$this->_build['alias'] = ' AS '.$str;
		}
		return $this;
	}

	public function order($str) {
		$this->_build['order'] = $str;
	}

	public function group($str) {
		$this->_build['group'] = $str;
	}

	public function join($str) {
		$this->_build['join'] .= $str;
	}

	public function limit($str) {
		$this->_build['limit'] = $str;
	}

	private function generateSql() {
		$build = $this->_build;
		switch ($this->_queryType) {
			case 's':
				$this->_sql = 'SELECT '.$build['fields'].' FROM `'.$this->_table.'`'.$build['alias'].$build['join'].$build['where'].$build['group'].$build['order'].$build['limit'];
				break;
			
			default:
				# code...
				break;
		}
	}
}
?>